import os
import sqlite3
import json

def ensure_directory_exists(path):
    directory = os.path.dirname(path)
    if not os.path.exists(directory):
        os.makedirs(directory)
#sqlite:///D:/AiFlies/memory/memory.db
def select_flie_tables(connection="sqlite:///D:/AiFlies/memory/memory.db"):
    # 提取数据库路径
    db_path = connection.replace("sqlite:///", "")
    
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        # 查询所有表
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
        tables = cursor.fetchall()
        print("数据库中的表:")
        for table in tables:
            print(table[0])
    conn.commit()


def select_tables(tablename='message_store',connection="sqlite:///D:/AiFlies/memory/memory.db"):
    # 提取数据库路径
    db_path = connection.replace("sqlite:///", "")
    
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        # 查询数据
        cursor.execute("SELECT * FROM  "+tablename)
        tables = cursor.fetchall()
        print("数据库中的表:",tables)
        for table in tables:
            # 假设表结构为 ()
            id,session_id, data, created_at=table
            #type, data, id, example = table[1]
            # 解析 message_data 为 JSON 对象
            message_json = json.loads(data)
            print(table[1])
            data = message_json.get("data", "")
            content = data.get("content", "")
            print(content)
        conn.commit()
def delete_tables(tablename = 'message_store',connection="sqlite:///D:/AiFlies/memory/memory.db"):
    # 提取数据库路径
    db_path = connection.replace("sqlite:///", "")
    
    with sqlite3.connect(db_path) as conn:
        ursor = conn.cursor()
        
        # 1. 删除表中所有数据（可选）
        ursor.execute(f"DELETE FROM {tablename}")  # 第一步：单独执行 DELETE
        
        # 2. 删除表结构（如果存在）
        ursor.execute(f"DROP TABLE IF EXISTS {tablename}")  # 第二步：单独执行 DROP TABLE
        
        conn.commit()
        print("操作完成")


def max_delete_tables0(session_id="36", max_records=1,tablename='message_store',connection="sqlite:///D:/AiFlies/memory/memory.db"):
    # 删除大于max_records多余的条数
    db_path = connection.replace("sqlite:///", "")
    
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        # 删除多余的条数
        cursor.execute(f'''
            DELETE FROM {tablename}
            WHERE created_at NOT IN (
                SELECT created_at
                FROM {tablename}
                WHERE session_id = ?
                ORDER BY created_at DESC
                LIMIT ?
            )
        ''', (session_id, max_records))
        rows_deleted = cursor.rowcount
        conn.commit()
        print(f"删除了 {rows_deleted} 条多余的记录")

def max_delete_tables(cursor,session_id="36", max_records=1,tablename='message_store'):
    # 删除多余的条数
    cursor.execute(f'''
    DELETE FROM {tablename}
        WHERE created_at NOT IN (
            SELECT created_at
            FROM {tablename}
            WHERE session_id = ?
            ORDER BY created_at DESC
            LIMIT ?
        )
    ''', (session_id, max_records))
    rows_deleted = cursor.rowcount
    print(f"删除了 {rows_deleted} 条多余的记录")
def delete_max_row(session_id,connection,max_records=5 ):
    # 提取数据库路径
    db_path = connection.replace("sqlite:///", "")
    # 连接到SQLite数据库（如果数据库不存在，则会自动创建）
    conn = sqlite3.connect(db_path)
    # 创建一个游标对象
    cursor = conn.cursor()
    #-统计所有记录的 data.content 字段总字节数 记录的 content 总长度 
    cursor.execute(f'''
        SELECT SUM(LENGTH(json_extract(message, '$.data.content')))  FROM message_store  WHERE session_id =  {session_id};
    ''')
    total_bytes = cursor.fetchone()[0]
    print(f"总字节数: {total_bytes}")
    if total_bytes > 90000:
        # 删除最旧的记录，直到总字节数不超过限制
        cursor.execute(f'''
            DELETE FROM message_store
            WHERE id IN (
                SELECT id
                FROM message_store
                WHERE session_id = {session_id}
                ORDER BY created_at ASC
                LIMIT {max_records}
            )
        ''')
        rows_deleted = cursor.rowcount
        conn.commit()
        print(f"删除了 {rows_deleted} 条多余的记录")
        conn.close()
select_flie_tables()
#max_delete_tables()
#delete_max_row(36,connection="sqlite:///D:/AiFlies/memory/memory.db")
#select_tables()
'''
 #-统计所有记录的 data.content 字段总字节数 记录的 content 总长度 
    
        SELECT SUM(LENGTH(json_extract(message, '$.data.content')))  FROM message_store  WHERE session_id = 36;
        
         lenstr = len(data.messages)
        # 计算所有消息的字符总数
        total_length = sum(len(message.content) for message in data.messages)
        print(f"{lenstr}条记录，符总数",total_length)
        # 如果超过限制，清理多余的历史记录
        if total_length > max_length:

'''